|
|
On our previous article we briefly touched on database roles and what they are, but more importantly, we started to talk about database principals. A database principal is essentially any entity or object that can request SQL Server resources. So what entities are grouped under the principal ‘moniker’? Anythig from database roles downright to database users.
For instance, if we take a look at the list of principals on a [master] database, we might see a listing like this. And if we look under [name] we see that the first value corresponds to [public]; [public] is a database_level role -there is also a certificate mapped user. But why bag roles & users together, if a user can be a member of one or more roles? Because resources can be controlled at database user and role levels alike, both offering varying grades of granularity.
 - sys.database_principals
The column definitions offered on the previous article should be rather self explanatory now that you can see actual data. Go ahead, query against one of your database’s sys.database_principals admin view and then compare it to what you see through SQL Server Management Studio for the same database.
Now, the next step in our quest is to figure out what kind of memberships exist for our principal / users -which groups do users belong to? Pretty easy! There is obviously another management view which exposes that information (think of it this way from here on: anything that’s done through SSMS, can be replicated by transact-sql means). In our case, principal-users and role relations are exposed through: sys.database_role_members.
1
| SELECT * FROM sys.database_role_members |
This view will expose the principal_id of the role matched against the principal_id of a user or other entity belonging to a role. And we will see how to exploit this very easily on the next article -I am tired & cranky right now.
 Share on Facebook
A question was raised not long ago on one of the boards I frequently visit, by one member who was trying to generate an report for his management team to audit database level roles & memberships -basically, who could do what and where?
The platform in question was Microsoft SQL Server 2005, and the key requirement was to be able to retrieve the same basic information that can be accessed via SQL Server Management Studio. So I set off to take a peek at the management views available in SQL 2005, and shortly after found what I was looking for.
At the database level, there are 2 basic objects that are extremely useful when it comes to roles & memberships. These are:
- sys.database_role_members - Returns one row for each member of each database role.
- sys.database_principals – Returns a row for each principal in a database.
At this point, I think its worth reviewing some concepts regarding SQL security related objects to make sure we are better prepared to absorb what we will be doing.
What is a Database Role?
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership; however, only members of the db_owner database role can add members to the db_owner fixed database role.
The fixed database roles are the following:
- db_accessadmin
- db_backupoperator
- db_datareader
- db_datawriter
- db_ddladmin
- db_denydatareader
- db_denydatawriter
- db_owner
- db_securityadmin
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.
Also, remember that database roles can be created using CREATE ROLE role_name and members assigned and removed from them. This is, for the time being, outside of the scope and purpose of this article.
What are Database Principals?
Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).
A database user is a principal at the database level. Every database user is a member of the public role.
So, now that we have a textbook -literally lifted from BOL- definition of what are database roles & principals, let’s inspect the data we get from our two management views.
Querying sys.database_principals
Let’s run the following snippet on any database, for arguments sake, let’s do so on our [master] database of any database instance we might have handy.
1
| SELECT * FROM sys.database_principals |
Our dataset will contain a listing of the following columns:
- name – data type: sysname
- Name of principal, unique within the database.
- principal_id – data type: int
- ID of principal, unique within the database.
- type – data type: char(1)
- Principal type: S = SQL user U = Windows user G = Windows group A = Application role R = Database role C = User mapped to a certificate K = User mapped to an asymmetric key
- type_desc – data type: nvarchar(60)
- Description of principal type: SQL_USER WINDOWS_USER WINDOWS_GROUP APPLICATION_ROLE DATABASE_ROLE CERTIFICATE_MAPPED_USER ASYMMETRIC_KEY_MAPPED_USER
- default_schema_name – data type: sysname
- Name to be used when SQL name does not specify schema. Null for principals not of type S, U, or A.
- create_date – data type: datetime
- Time at which the principal was created.
- modify_date – data type: datetime
- Time at which the principal was last modified.
- owning_principal_id – data type: int
- ID of the principal that owns this principal. All principals except Database Roles must be owned by dbo.
- sid – data type: varbinary(85)
- SID (Security Identifier) if the principal is defined external to the database (type S, U, and G). Otherwise, NULL.
- is_fixed_role – data type: bit
- If 1, then this row represents an entry for one of the fixed database roles: db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter.
Let’s review the key elements on the next article…
Share on Facebook
This little query can be very handy sometimes.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| DECLARE @INSTANCENAME AS VARCHAR(20)
DECLARE @KeyString as VARCHAR(500)
SELECT @INSTANCENAME = RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))
SET @keystring = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @INSTANCENAME + '\MSSQLServer\SuperSocketNetLib\Tcp'
DECLARE @port varchar(5)
EXEC xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@keystring,
@value_name='TcpPort',
@value=@port OUTPUT
PRINT @port + '/' + @instancename |
Share on Facebook
This little script’s helped me numerous times when refreshing environments manually from backups and had to ensure there were no left-over connections left which would’ve made the restores fail. Without further a due:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| DECLARE @dbspid AS int
DECLARE @killcommand AS varchar(50)
DECLARE ConnectionKiller CURSOR FOR
SELECT sysproc.spid FROM sysprocesses AS sysproc
INNER JOIN sysdatabases AS sysdb ON
sysdb.dbid = sysproc.dbid
WHERE sysdb.NAME IN ('[DBNAME]')
AND sysproc.spid <> 0
UNION
SELECT sysproc.blocked FROM sysprocesses AS sysproc
INNER JOIN sysdatabases AS sysdb ON
sysdb.dbid = sysproc.dbid
WHERE sysdb.NAME IN ('[DBNAME]')
AND sysproc.blocked<>0
OPEN ConnectionKiller
FETCH NEXT FROM ConnectionKiller INTO @dbspid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @killcommand = 'KILL '+CAST(@dbspid AS VARCHAR(50))
EXEC (@killcommand)
FETCH NEXT FROM ConnectionKiller INTO @dbspid
END
CLOSE ConnectionKiller
DEALLOCATE ConnectionKiller |
Share on Facebook
I’ve seen quite a bit of confusion as to the purpose and application of the four basic JOIN operations available for transact SQL. Even I dread coming out of my main comfort zone (my beloved INNER JOIN) sometimes. Nonetheless, JOINS are one of the most essential building blocks to some of the more complex data manipulation operations that transact-SQL has to offer. Now, below is my pre-K attempt at documenting their use, in a simplistic and easy to understand manner. It is not perfect, and it is geared towards the Microsoft SQL interpretation of the ANSI SQL standards.
 Venn Diagram - SQL Joins Explained
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
| CREATE TABLE #tmpSpanish (numNumber int,txtNumber varchar(20))
INSERT INTO #tmpSpanish (numNumber,txtNumber) VALUES (1,'Uno')
INSERT INTO #tmpSpanish (numNumber,txtNumber) VALUES (2,'Dos')
INSERT INTO #tmpSpanish (numNumber,txtNumber) VALUES (3,'Tres')
INSERT INTO #tmpSpanish (numNumber,txtNumber) VALUES (4,'Cuatro')
INSERT INTO #tmpSpanish (numNumber,txtNumber) VALUES (5,'Cinco')
INSERT INTO #tmpSpanish (numNumber,txtNumber) VALUES (6,'Seis')
INSERT INTO #tmpSpanish (numNumber,txtNumber) VALUES (7,'Siete')
CREATE TABLE #tmpEnglish (numNumber int,txtNumber varchar(20))
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (4,'Four')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (5,'Five')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (6,'Six')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (7,'Seven')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (8,'Eight')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (9,'Nine')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (10,'Ten')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (11,'Eleven')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (12,'Twelve')
INSERT INTO #tmpEnglish (numNumber,txtNumber) VALUES (13,'Thirteen')
/*
An INNER JOIN will kindly return all rows which satisfy the JOIN condition on both tables.
Picture a Venn Diagram, with three areas: A, B & C. B contains all the overlapping elements
from A & B. This is what an INNER JOIN will return.
*/
SELECT * FROM #tmpSpanish
INNER JOIN #tmpEnglish
ON #tmpSpanish.numNumber = #tmpEnglish.numNumber
/*
A LEFT JOIN will return all rows which exist in the left-most table:
(LEFT) (RIGHT)
ON #tmpSpanish.numNumber = #tmpEnglish.numNumber
Plus, all the common elements to both tables. Picture a Venn Diagram,
with three areas: A, B & C. B contains all the overlapping elements
from A & C. This join, will return all the elements from A & all the
elements from B.
*/
SELECT * FROM #tmpSpanish
LEFT JOIN #tmpEnglish
ON #tmpSpanish.numNumber = #tmpEnglish.numNumber
/*
A RIGHT JOIN will return all rows which exist in the right-most table:
(LEFT) (RIGHT)
ON #tmpSpanish.numNumber = #tmpEnglish.numNumber
Plus, all the common elements to both tables. Picture a Venn Diagram,
with three areas: A, B & C. B contains all the overlapping elements
from A & C. This join, will return all the elements from C & all the
elements from B.
*/
SELECT * FROM #tmpSpanish
RIGHT JOIN #tmpEnglish
ON #tmpSpanish.numNumber = #tmpEnglish.numNumber
/*
A FULL OUTER JOIN will return all rows which exist on both tables, left & right-most.
(LEFT) (RIGHT)
ON #tmpSpanish.numNumber = #tmpEnglish.numNumber
Including all the common elements to both tables. Picture a Venn Diagram,
with three areas: A, B & C. B contains all the overlapping elements
from A & C. This join essentially returns all of the elements from A & C, including
those common (B) to both.
*/
SELECT * FROM #tmpSpanish
FULL OUTER JOIN #tmpEnglish
ON #tmpSpanish.numNumber = #tmpEnglish.numNumber
DROP TABLE #tmpSpanish
DROP TABLE #tmpEnglish |
Share on Facebook
|
|
