tonight i was working on compressing indexes for a sharepoint database. I ran into some issue because some of the columns were using sparse data type, which is great for saving space on null values. Because of this, you can’t compress the table or indexes.
the following script will help you identify which table has sparse columns instead of looking thru it one at time
select distinct o.name from sys.columns c
right join sys.objects o
on c.object_id = o.object_id
where o.type = ‘U’
and COLUMNPROPERTY( OBJECT_ID(o.name),c.name,’issparse’) = 1
This past weekend, I had to migrate a sql instance from an old UCS blade to a new one. The new blade would boot from the ZFS san instead of the retarded netapp san. It might be retarded because of all the hands that has been in it but suffice to say, it suck.
I ran into two unknowns during this migration
I have a server here at cbtr.net that has multiple sql named instances and only 1 IP address. All sql instances are listening on that IP address and they are all currently on dynamic ports. I have created a static DNS entry that looks like this:
CDev A 10.1.1.222
10.1.1.222 is assigned to a server name HorseShoe. The idea is to have the devs connect to CDev instead of connecting to HorseShoe\Instance1.
Last week, apparently I messed up revoking some permission and ended up denying it instead so this week, I’m having to go find which user has denied permission when it should of been revoke. The following query got me thru it:
SELECT USER_NAME(grantee_principal_id) AS ‘User’
, state_desc AS ‘Permission’
, permission_name AS ‘Action’
, CASE class
WHEN 0 THEN ‘Database::’ + DB_NAME()
WHEN 1 THEN OBJECT_NAME(major_id)
WHEN 3 THEN ‘Schema::’ + SCHEMA_NAME(major_id)
END AS ‘Securable’
FROM sys.database_permissions dp
WHERE class IN ( 0, 1, 3 )
AND minor_id = 0
AND state_desc = ‘deny’
This morning, I was working with a dev. He was trying to use SQL Compare and it said he didn’t have view definition permission. I was looking thru the security group for the specific database and could identify which group he was in. I ran the following script and was able to find him:
EXECUTE AS LOGIN = ‘domainA\UserA’
SELECT * FROM fn_my_permissions (NULL, ‘DATABASE’);
SELECT * FROM sys.user_token
The user_token view will tell you what group or role the user is part off. The fn_my_permissions(null,’database’) will tell you what permissions you have for that specific database.